package com.one.mycommons;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author chen
 * excel2007鎿嶄綔
 */
public class ExcelOperation {
	
	/**
	 * @param workbook
	 * @param excelInitRow
	 * @param list
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public static XSSFWorkbook writeExcelTemplate(XSSFWorkbook workbook, int excelInitRow, List list) throws FileNotFoundException, IOException, IllegalArgumentException, IllegalAccessException {
		
		XSSFSheet sheet = workbook.getSheetAt(0);//getSheet("Sheet1");
	    XSSFRow row;
		XSSFCell cell = null;
		int size = list.size();
		int lengths = 0;
		XSSFCellStyle style = getStyle(workbook);
		for (int i = 0; i < size; i++) {
			row = sheet.createRow(excelInitRow);
			Object obj = list.get(i);
			Field[] fields = obj.getClass().getDeclaredFields();
			lengths  = fields.length;
			for (int j = 0; j < lengths; j++) {
				fields[j].setAccessible(true);
				cell = row.createCell((short) j,HSSFCellStyle.ALIGN_CENTER);
			    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			    Object value = fields[j].get(obj);
			    if (null != value) {
			    	//int鍨�
			    	if (fields[j].getType().toString().contains("Integer")||fields[j].getType().toString().contains("int")) {
			    		cell.setCellValue(String.valueOf(value));
					}
			    	else {
			    		cell.setCellValue(value.toString());
					}
				}
			    else {
			    	cell.setCellValue("");
				}
			    cell.setCellStyle(style);
			}
			//涓嬩竴琛�
			excelInitRow++;
		}
		for (int k = 0; k < lengths; k++) {
			sheet.autoSizeColumn(k);
			int width = sheet.getColumnWidth(k);
			if (width < 1600) {
				sheet.setColumnWidth(k, 1600);
			}else {
				sheet.setColumnWidth(k, width + 1000);
			}
		}
		return workbook; 
	}

	/**
	 * @param workbook
	 * @return
	 */
	private static XSSFCellStyle getStyle(XSSFWorkbook workbook) {
//	     璁剧疆瀛椾綋;
		  XSSFFont font = workbook.createFont();
		  //璁剧疆瀛椾綋澶у皬;
		  font.setFontHeightInPoints((short)9);
		  //璁剧疆瀛椾綋鍚嶅瓧;
		  font.setFontName("Courier New");
		  //font.setItalic(true);
		  //font.setStrikeout(true);
//		     璁剧疆鏍峰紡;
		  XSSFCellStyle style = workbook.createCellStyle();
		  //璁剧疆搴曡竟妗�;
		  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		  //璁剧疆搴曡竟妗嗛鑹�;
		  style.setBottomBorderColor(HSSFColor.BLACK.index);
		  //璁剧疆宸﹁竟妗�;
		  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		  //璁剧疆宸﹁竟妗嗛鑹�;
		  style.setLeftBorderColor(HSSFColor.BLACK.index);
		  //璁剧疆鍙宠竟妗�;
		  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		  //璁剧疆鍙宠竟妗嗛鑹�;
		  style.setRightBorderColor(HSSFColor.BLACK.index);
		  //璁剧疆椤惰竟妗�;
		  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		  //璁剧疆椤惰竟妗嗛鑹�;
		  style.setTopBorderColor(HSSFColor.BLACK.index);
		  //鍦ㄦ牱寮忕敤搴旂敤璁剧疆鐨勫瓧浣�;
		  style.setFont(font);
		     //璁剧疆鑷姩鎹㈣;
		  style.setWrapText(false);
		  //璁剧疆姘村钩瀵归綈鐨勬牱寮忎负灞呬腑瀵归綈;
		  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		  //璁剧疆鍨傜洿瀵归綈鐨勬牱寮忎负灞呬腑瀵归綈;
		  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		  return style;
	}
	
	/**
	 * @param workbook
	 * @param excelInitRow
	 * @param classz
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static List readExcel4Update(XSSFWorkbook workbook, int excelInitRow, Class<?> classz) throws InstantiationException, IllegalAccessException {
		List list = null;
		 //璇诲彇宸ヤ綔琛�
		XSSFSheet sheet = workbook.getSheetAt(0);//getSheet("Sheet1");
	    XSSFRow row;
		XSSFCell cell = null;
		//excel鎬昏鏁�
		int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        //excel鎬诲垪鏁�
        int colNum = row.getPhysicalNumberOfCells();
        // 濉厖鏁版嵁鐨勭被鐨� 灞炴�т釜鏁�
        int objColl = classz.getDeclaredFields().length;
        //濡傛灉鏈夋暟鎹�
        if (rowNum > excelInitRow) {
			list =  new ArrayList<Object>();
			//閬嶅巻姣忚鏁版嵁
	        for (int i = excelInitRow; i < rowNum; i++) {
	        	//鑾峰緱褰撳墠琛�
	        	row = sheet.getRow(i);
	        	cell = row.getCell(0);
	        	if ("-".equals(getValue(cell)) || "".equals(getValue(cell))) {
					continue;
				}
	        	//瀹炰緥鍖�
	        	Object newObj = classz.newInstance();
	        	Field[] fields = newObj.getClass().getDeclaredFields();
	        	boolean isAddObj = false;
	        	//閬嶅巻姣忓垪鐨勬暟鎹�
	        	for (int j = 0; j < objColl; j++) {
	        		cell = row.getCell(j);
	        		if (null != cell) {
		        		fields[j].setAccessible(true);
		        		String cellString = getValue(cell);
		        		if (!"".equals(cellString)) {
		        			isAddObj = true;
			        		//int鍨�
					    	if (fields[j].getType().toString().contains("Integer") ||fields[j].getType().toString().contains("int")) {
					    		double dnum = Double.valueOf(cellString);
					    		int inum = (int) dnum;
					    		fields[j].set(newObj, inum);
							}
					    	//double鍨�
					    	else if (fields[j].getType().toString().contains("Double") ||fields[j].getType().toString().contains("double")) {
					    		fields[j].set(newObj, Double.valueOf(cellString));
							}
					    	//boolean鍨�
					    	else if (fields[j].getType().toString().contains("boolean") ||fields[j].getType().toString().contains("Boolean")) {
					    		fields[j].set(newObj, Boolean.valueOf(cellString));
							}
					    	else {
					    		fields[j].set(newObj, cellString);
							}
		        		}
		        		
	        		}
				}
	        	if (isAddObj) {
	        		list.add(newObj);
				}
			}
        }
		return list;
	}

	/**
	 * @param cell
	 * @return
	 */
	private static String getValue(XSSFCell cell) {
		String value ="";
		switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_NUMERIC:
				value = String.valueOf(cell.getNumericCellValue());
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case XSSFCell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			default:
				break;
		}
		return value.trim();
	}
}
